# coding=utf-8
# 代码文件：chapter3/ch3.2.13.py
from datetime import date

import xlwings as xw
import re

# 匹配日期正则表达式，例如：2004-04-30 | 2004-02-29
pattern = r'^[0-9]{4}-(((0[13578]|(10|12))-(0[1-9]|[1-2][0-9]|3[0-1]))|(02-(0[1-9]|[1-2][0-9]))|((0[469]|11)-(0[1-9]|[1-2][0-9]|30)))$'

app = xw.App(visible=False, add_book=False)
f = r'data/学生信息.xlsx'
wb = app.books.open(f)

sheet1 = wb.sheets['Sheet1']

rng = sheet1.range('A1').current_region

# 遍历单元格
for cell in rng:

    # 取出单元格数据
    data = cell.options(numbers=int, dates=date).value
    if cell.address.startswith('$C'):  # 判断是否是第C列

        m = re.search(pattern, str(data))
        # 不匹配
        if m is None:
            cell.color = (255, 255, 0)

f = r'data/学生信息2.xlsx'
wb.save(path=f)

# 关闭工作簿对象
wb.close()
# 退出Excel应用程序
app.quit()

print('Game Over')
